﻿using System;
using System.Linq;
using System.Data.Linq;
using UniMana.Data;

namespace UniMana.Data
{
    public static class Catalogue_LevelData
    {
        /// <summary>
        /// Lấy bậc đào tạo với khoa
        /// </summary>
        /// <param name="nLevel"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iDepartment"></param>
        /// <returns></returns>

        public static IQueryable GetLevelByDepartment(
            this Table<Catalogue_Level> nLevel , 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iDepartment)
        {
            var level = (from t in nMajor
                         join t0 in nTrainingProgram on t.Catalogue_MajorKey  equals t0.Catalogue_MajorKey
                         join t1 in nLevel on t.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                         where
                           t.Catalogue_DepartmentKey == m_iDepartment
                         select new
                         {
                             t0.Catalogue_LevelKey,
                             t1.Name,
                         }).Distinct();
            return level.Distinct();
        }

        /// <summary>
        /// Lấy bậc đào tạo với ngành
        /// </summary>
        /// <param name="nLevel"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iMajor"></param>
        /// <returns></returns>

        public static IQueryable GetLevelByMajor(
            this Table<Catalogue_Level> nLevel, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iMajor)
        {
            var level = (from t in nMajor
                         join t0 in nTrainingProgram on  t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                         join t1 in nLevel on t.Catalogue_LevelKey equals t1.Catalogue_LevelKey 
                         where
                           t0.Catalogue_MajorKey == m_iMajor
                         select new
                         {
                             t0.Catalogue_LevelKey,
                             t1.Name,
                         }).Distinct();
            return level.Distinct();
        }

        /// <summary>
        /// Lấy bậc đào tạo với loại hình đào tạo
        /// </summary>
        /// <param name="nLevel"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iTrainingType"></param>
        /// <returns></returns>

        public static IQueryable GetLevelByTrainingType(
            this Table<Catalogue_Level> nLevel, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iTrainingType)
        {
            var level = (from t in nMajor
                         join t0 in nTrainingProgram on  t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                         join t1 in nLevel on t.Catalogue_LevelKey equals  t1.Catalogue_LevelKey 
                         where
                           t0.Catalogue_TrainingTypeKey == m_iTrainingType
                         select new
                         {
                             t0.Catalogue_LevelKey,
                             t1.Name,
                         }).Distinct();
            return level.Distinct();
        }

        /// <summary>
        /// Lấy bậc đào tạo với khóa học
        /// </summary>
        /// <param name="nLevel"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="m_iCourse"></param>
        /// <returns></returns>

        public static IQueryable GetLevelByCourse(
            this Table<Catalogue_Level> nLevel, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iCourse)
        {
            var level = (from t in nMajor
                         join t0 in nTrainingProgram on t.Catalogue_MajorKey  equals t0.Catalogue_MajorKey
                         join t1 in nLevel on t.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                         where
                           t0.Catalogue_CourseKey == m_iCourse
                         select new
                         {
                             t0.Catalogue_LevelKey,
                             t1.Name,
                         }).Distinct();
            return level.Distinct();
        }

        /// <summary>
        /// Lấy bậc đào tạo có trong chương trình đào tạo
        /// </summary>
        /// <param name="nLevel"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <returns></returns>

        public static IQueryable GetLevelInTrainingProgram(
            this Table<Catalogue_Level> nLevel, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor)
        {
            var level = (from t in nTrainingProgram
                         join t0 in nMajor on t.Catalogue_MajorKey equals t0.Catalogue_MajorKey
                         join t1 in nLevel on t.Catalogue_LevelKey equals t1.Catalogue_LevelKey
                         select new
                         {
                             t.Catalogue_LevelKey,
                             t1.Name
                         }).Distinct();
                return level.Distinct();
        }
        /// <summary>
        /// Lấy bậc đào tạo với khoa, ngành, loại hình, khóa học
        /// </summary>
        /// <param name="nLevel"></param>
        /// <param name="nTrainingProgram"></param>
        /// <param name="nMajor"></param>
        /// <param name="lueCatalogueDepartment"></param>
        /// <param name="lueCatalogueMajor"></param>
        /// <param name="lueCatalogueTrainingType"></param>
        /// <param name="lueCatalogueCourse"></param>
        /// <returns></returns>

        public static IQueryable GetLevelByDeparmentMajorTrainingTypeCourse(
            this Table<Catalogue_Level> nLevel, 
            Table<Training_TrainingProgram> nTrainingProgram, 
            Table<Catalogue_Major> nMajor, 
            int m_iDepartment, 
            int m_iMajor, 
            int m_iTrainingType, 
            int m_iCourse)
        {
            var level = (from t in nTrainingProgram
                         join t0 in nMajor on t.Catalogue_MajorKey equals  t0.Catalogue_MajorKey 
                         join t1 in nLevel on t.Catalogue_LevelKey equals  t1.Catalogue_LevelKey 
                         group new { t, t1, t0 } by new
                         {
                             t.Catalogue_LevelKey,
                             t1.Name,
                             t0.Catalogue_DepartmentKey,
                             t.Catalogue_TrainingTypeKey,
                             t.Catalogue_CourseKey,
                             t.Catalogue_MajorKey
                         } into g
                         select new
                         {
                             g.Key.Catalogue_LevelKey,
                             g.Key.Name,
                             g.Key.Catalogue_DepartmentKey,
                             g.Key.Catalogue_MajorKey,
                             g.Key.Catalogue_TrainingTypeKey,
                             g.Key.Catalogue_CourseKey
                         }).Distinct();

            if (m_iDepartment > 0)
            {
                level = level.Where(p => p.Catalogue_DepartmentKey == int.Parse(m_iDepartment.ToString()));
            }
            if (m_iMajor > 0)
            {
                level = level.Where(p => p.Catalogue_MajorKey == int.Parse(m_iMajor.ToString()));
            }
            if (m_iTrainingType > 0)
            {
                level = level.Where(p => p.Catalogue_TrainingTypeKey == int.Parse(m_iTrainingType.ToString()));
            }
            if (m_iCourse > 0)
            {
                level = level.Where(p => p.Catalogue_CourseKey == int.Parse(m_iCourse.ToString()));
            }

            var a = (from lv in level
                     select new
                     {
                         lv.Catalogue_LevelKey,
                         lv.Name
                     }).Distinct();
            return a.Distinct() ;
        }
    }
}
